package com.dimples.strategy.service;

import com.dimples.constant.SQLConstant;
import com.dimples.strategy.BuildTableStrategyService;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;
import java.util.Optional;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

/**
 * @author zhongyj <1126834403@qq.com><br/>
 * @date 2021/2/20
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class CacheFileBuildTableServiceImpl implements BuildTableStrategyService {

    private final JdbcTemplate jdbcTemplate;

    public static final String SQL_PATH = "F:\\Java\\workspace\\dimples-auto-database\\data\\test-build.sql";

    @Override
    public List<String> create(MultipartFile file, String sql) {
        FileUtil.del(SQL_PATH);
        List<String> msg = CollUtil.newArrayList();
        if (ObjectUtil.isNotEmpty(file)) {
            log.info("========================== 开始解析SQL脚本文件 ================================");
            String filename = FileUtil.getTmpDirPath() + file.getOriginalFilename();
            try {
                // 上传文件
                FileUtil.writeFromStream(file.getInputStream(), filename);

                List<String> lines = CollUtil.removeBlank(FileUtil.readUtf8Lines(filename));
                List<String> createSql = CollUtil.newArrayList();
                List<String> insertSql = CollUtil.newArrayList();

                String sqlExe = "";
                String tableName = "";
                // 是否为建表语句标记
                boolean flag = false;
                int lineNum = -1;
                for (String line : lines) {
                    if (StrUtil.isBlank(line)) {
                        continue;
                    }
                    lineNum = lineNum + 1;
                    // 建表语句开始
                    if (StrUtil.containsIgnoreCase(line, "CREATE TABLE ")) {
                        flag = true;
                        // 获取并处理表名
                        tableName = Optional
                                .of(StrUtil.subAfter(line, SQLConstant.SQL_CREATE_TABLE.toLowerCase(), false))
                                .orElse(StrUtil.subAfter(line, SQLConstant.SQL_CREATE_TABLE.toUpperCase(), false));
                        createSql.add(0, "\nDROP TABLE IF EXISTS `" + tableName + "`;");
                        createSql.add(line);
                        continue;
                    }
                    // 建表语句结束
                    if (lineNum == lines.size() - 1) {
                        flag = false;
                        sqlExe = CollUtil.join(createSql, "\n");
                        createSql.clear();
                    } else if (StrUtil.containsIgnoreCase(lines.get(lineNum + 1), "CREATE TABLE ")) {
                        flag = false;
                        sqlExe = CollUtil.join(createSql, "\n");
                        createSql.clear();
                    }

                    // 建表字段，处理每一行
                    String tableComment = "";
                    if (flag) {
                        // 根据正则处理
                        for (String key : SQLConstant.cacheToMysqlRegReplace.keySet()) {
                            List<String> reg = ReUtil.findAllGroup0(key, line);
                            if (CollUtil.isNotEmpty(reg) && StrUtil.isNotBlank(reg.get(0))) {
                                if (StrUtil.containsIgnoreCase(line, "INTEGER") && StrUtil.containsIgnoreCase(key, "default")) {
                                    line = StrUtil.replace(line, reg.get(0), " AUTO_INCREMENT ");
                                }
                                line = StrUtil.replace(line, reg.get(0), SQLConstant.cacheToMysqlRegReplace.get(key));
                            }
                        }
                        // 处理constraint
                        if (StrUtil.containsIgnoreCase(line, "constraint")
                                || StrUtil.containsIgnoreCase(line, "foreign key")
                                || StrUtil.containsIgnoreCase(line, "on update cascade on delete cascade")) {
                            continue;
                        }
                        // 处理comment
                        if (StrUtil.containsIgnoreCase(line, "comment on")) {
                            // 注释
                            List<String> comment = ReUtil.findAllGroup0("\\'.*\\'", line);
                            if (StrUtil.containsIgnoreCase(line, "comment on table") && CollUtil.isNotEmpty(comment)) {
                                tableComment = comment.get(0);
                                for (int i = 0; i < createSql.size(); i++) {
                                    if (StrUtil.containsIgnoreCase(createSql.get(i), "ENGINE = InnoDB")) {
                                        createSql.set(i, StrUtil.removeSuffix(createSql.get(i), ";") + " comment " + tableComment + ";");
                                        break;
                                    }
                                }
                            }
                            if (StrUtil.containsIgnoreCase(line, "comment on column") && CollUtil.isNotEmpty(comment)) {
                                // 获取建表的列名
                                String rowName = StrUtil.splitTrim(StrUtil.splitTrim(line, " ").get(3), ".").get(1);
                                // 遍历已有的语句，将注释插入
                                for (int i = 0; i < createSql.size(); i++) {
                                    List<String> split = StrUtil.splitTrim(createSql.get(i), " ");
                                    if (CollUtil.contains(split, rowName)) {
                                        createSql.set(i, StrUtil.removeSuffix(createSql.get(i), ",") + " comment " + comment.get(0) + ",");
                                        break;
                                    }
                                }
                            }
                        } else {
                            // 处理引擎设置
                            if (StrUtil.containsIgnoreCase(line, ");")) {
                                createSql.set(createSql.size() - 1, StrUtil.removeSuffix(createSql.get(createSql.size() - 1), ","));
                                createSql.add(") ENGINE = InnoDB;");
                                continue;
                            } else {
                                createSql.add(line);
                            }
                        }
                    }
                    // 建表语句完成
                    if (StrUtil.isNotBlank(sqlExe)) {
                        try {
                            log.info("\n================================ 开始创建表【{}】===================================\n"
                                    , StrUtil.removeSuffix(tableName, "("));
//                            this.jdbcTemplate.execute(sqlExe);
                            FileUtil.appendUtf8String(sqlExe, SQL_PATH);
                        } catch (Exception e) {
                            log.info("创建表【{}】失败，信息为：{}\n", tableName, e.getMessage());
                            msg.add(StrUtil.format("创建表【{}】失败，信息为：【{}】\n", tableName, e.getMessage()));
                            sqlExe = "";
                            continue;
                        }
                        sqlExe = "";
                        tableName = "";
                    }
                }

            } catch (IOException e) {
                throw new RuntimeException("文件解析错误", e);
            }
        }
        if (CollUtil.isEmpty(msg)) {
            msg.add("成功");
        }
        return msg;
    }
}






















